create table UF (
  UF char(2) primary key,
  nome varchar(256) not null,
)

create table Endereco (
  idEndereco int primary key identity,
  bairro varchar(256),
  numero int,
  logradouro varchar(256),
  CEP char(8),

  complemento varchar(256),  

  -- Uma descrição de como pode-se achar o lugar que vai alguém do que é comum de se colocar em endereços, como
  -- "na frente da casa de fulano", ou "perto do hospital tal".
  outrasInformacoes varchar(256),
  
  cidade varchar(256),
  
  UF char(2) foreign key references UF(UF)
)

create table Telefone (
  idTelefone int primary key identity,
  numero varchar(30) not null,
  
  -- Telefone de casa, do trabalho, celular da empresa, ...
  -- Isso é uma mera descrição para visualização.
  tipo varchar(256)
)

create table Matriz (
  idMatriz int primary key identity,
  razaoSocial varchar(256),
  CNPJ char(14),
  senha varchar(256) not null,
  ativo bit not null default 1
)

create table Filial (
  idFilial int primary key identity,
  senha varchar(256) not null,
  nome varchar(256) not null,
  
  -- Relacionamento Localiza (Filial-Endereço).
  endereco int foreign key references Endereco(idEndereco),
  
  ativo bit not null default 1
)

create table ContantoFilial (
  idFilial int not null foreign key references Filial(idFilial),
  idTelefone int not null foreign key references Telefone(idTelefone),
  ativo bit not null default 1
)

create table TipoModelo (
  idTipoModelo int primary key identity,

  -- Se é utilitário, luxo, popular,etc.
  nome varchar(256) not null,

  -- Usado para especialização.
  -- Pode ser "utilitario" ou null.
  subtipo varchar(32),
  
  ativo bit not null default 1
)

create table Modelo (
  idModelo int primary key identity,
  marca varchar(256) not null,
  nome varchar(256) not null,

  -- Categoria: A-E. Isso é relativo à carteira de habilitação.
  categoria varchar(2) not null,

  -- Como lidar com dinheiro? int, decimal, money, .... ?
  precoDiaria money not null,
  precoKm money not null,

  -- Relacionamento Tipagem (Modelo-TipoModelo).
  tipoModelo int not null foreign key references TipoModelo(idTipoModelo),
  
  ativo bit not null default 1
)

create table ModeloUtilitario (
  idTipoModelo int not null foreign key references TipoModelo(idTipoModelo),
  idModelo int not null foreign key references Modelo(idModelo),
  carga int,
  lotacao int
)

create table Veiculo (
  idVeiculo int primary key identity,
  placa char(8) not null,
  kilometragemInicial int not null,
  kilometragemAtual int not null,
  vencimentoSeguro datetime,
  ano int,
  custoCompra money,

  -- Relacionamento Modelo Associado (Veiculo-Modelo).
  modelo int not null foreign key references Modelo(idModelo),

  -- Relacionamento Frota (Filial-Veiculo).
  -- Deve guardar qual a filial que o carro está, ou qual a última filial que o carro esteve.
  filial int not null foreign key references Filial(idFilial),
  
  ativo bit not null default 1
)

create table Manutencao (
  idManutencao int primary key identity,
  dataInicio datetime not null,
  custo money,

  -- Opcionamente, pode-se dizer o motivo pelo qual o carro foi enviado para manutenção.
  motivo varchar(4096),

  -- Uma manutencao com data nula é dada como uma manutenção em acontecimento (i.e. não terminada).
  dataFim datetime,

  -- Qual é a kilometragem do carro na hora do envio?
  kilometragemEnvio int not null,

  -- Relacionamento Conserto (Veiculo-Manutencao).
  veiculo int not null foreign key references Veiculo(idVeiculo),
  
  ativo bit not null default 1
)

create table Cliente (
  idCliente int primary key identity,
  email varchar(256) not null,
  
  -- Relacionamento Endereçamento (Cliente-Endereço)
  endereco int foreign key references Endereco(idEndereco),
  
  -- Dados de informação sobre o cartão de crédito.
  dataValidadeCartao datetime,
  bandeiraCartao varchar(128),
  numeroCartao varchar(32),
  codigoSegurancaCartao varchar(8),
  
  -- Uma senha nula é permitida, mas então acesso pelo website não pode ser feito.
  senha varchar(256),
  
  -- Usado para especialização
  -- "fisica" ou "juridica".
  tipo varchar(32) not null,
  
  ativo bit not null default 1
)

create table ContantoCliente (
  idCliente int not null foreign key references Cliente(idCliente),
  idTelefone int not null foreign key references Telefone(idTelefone),
  
  ativo bit not null default 1
)

create table Sexo (
  idSexo int primary key identity,
  descricao varchar(32) not null
)

create table PessoaFisica (
  -- Especializacao
  idCliente int primary key foreign key references Cliente(idCliente),
  
  CPF char(11) not null,
  nome varchar(256) not null,
  idade int,
  sexo int foreign key references Sexo(idSexo),
  
  -- Usado apra especialização.
  -- "condutor" ou null.
  tipo varchar(32)
)

create table PessoaJuridica (
  -- Especialização
  idCliente int primary key foreign key references Cliente(idCliente),
  
  CNPJ char(14) not null,
  razaoSocial varchar(256) not null
)

create table Condutor (
  -- Especialização
  idCliente int primary key foreign key references Cliente(idCliente),
  
  vencimentoCNH datetime not null,
  CNH varchar(32) not null,
  categoriaCNH varchar(8) not null,
)

-- Relacionamento: Conduz para.
create table ConduzPara (
  idCondutor int not null foreign key references Condutor(idCliente),
  idCliente int not null foreign key references Cliente(idCliente),
  ativo bit not null default 1
)

create table Reserva (
  idReserva int primary key identity,
  cancelada bit not null default 0,
  previsaoInicio datetime not null,
  previsaoTermino datetime not null,
  
  -- Relacionamento: Modelo Preferenciado (Reserva-Modelo).
  modelo int not null foreign key references Modelo(idModelo),

  -- Relacionamento: Requisição (Reserva-Cliente)
  cliente int not null foreign key references Cliente(idCliente),

  -- Relacionamento: Devolução (Reserva-Filial)
  filialDevolucao int foreign key references Filial(idFilial),

  -- Relacionamento: Obtenção (Reserva-Filial)
  filialObtencao int foreign key references Filial(idFilial),
  
  ativo bit not null default 1
)


create table Locacao (
  idLocacao int primary key identity,
  kilometragemDevolucao int,
  dataDevolucao datetime,
  dataObtencao datetime not null,
  kilometragemObtencao int not null,  

  -- Relacionamento: Requisição (Locacao-Veículo).
  veiculo int not null foreign key references Veiculo(idVeiculo),

  -- Relacionamento: Efetivação.
  efetivacao int not null foreign key references Reserva(idReserva),

  -- Relacionamento: Condutor.
  condutor int not null foreign key references Condutor(idCliente),
)

-- A criação de uma tabela para o relacionamento Pagamento não é necessário, mas parece que facilita as coisas.
create table Pagamento (
  idPagamento int primary key identity,
  dataPagamento datetime not null,
  taxaExtra money not null default 0,
  valor money not null,

  -- Modo que se deve calcular o preço.
  -- "km" ou "diaria"
  tipoCalculo varchar(32) not null,
  
  -- Modo de pagamento: Cartão, dinheiro, etc.
  -- Deve ser "cartao", "dinheiro" ou "debito"
  modoPagamento varchar(32) not null,

  -- Quem pagou?
  cliente int not null foreign key references Cliente(idCliente),

  -- Relacionamento: Pagamento.
  locacao int foreign key references Locacao(idLocacao)
)

go

create view EnderecoCidadeUF as
select Endereco.idEndereco as idEndereco,
       Endereco.bairro as bairro,
       Endereco.numero as numero,
       Endereco.logradouro as logradouro,
       Endereco.CEP as CEP,
       Endereco.complemento as complemento,
       Endereco.outrasInformacoes as outrasInformacoes,
       Endereco.cidade as nomeCidade,
       UF.UF as UF,
       UF.nome as nomeUF
from Endereco
     left join
     UF on Endereco.UF = UF.UF

go

-- Vários dados de clientes, incluindo dados das tabelas:
--  Cliente, PessoaFisica, PessoaJuridica, Endereco, Condutor, Sexo,
create view DadosCliente as
select Cli.idCliente as idCliente,
       Cli.email as email,
       Cli.dataValidadeCartao as dataValidadeCartao,
       Cli.bandeiraCartao as bandeiraCartao,
       Cli.numeroCartao as numeroCartao,
       Cli.codigoSegurancaCartao as codigoSegurancaCartao,
       Cli.tipo as tipoCliente,
       Cond.vencimentoCNH as vencimentoCNH,
       Cond.CNH as CNH,
       Cond.categoriaCNH as categoriaCNH,
       Sexo.descricao as sexo,
       E.idEndereco as idEndereco,
       E.bairro as bairro,
       E.numero as numero,
       E.logradouro as logradouro,
       E.CEP as CEP,
       E.complemento as complemento,
       E.outrasInformacoes as outrasInformacoes,
       E.nomeCidade as nomeCidade,
       E.UF as UF,
       E.nomeUF as nomeUF,
       PF.CPF as CPF,
       PF.nome as nomeCliente,
       PF.idade as idade,
       PF.tipo as tipoPF,
       PJ.CNPJ as CNPJ,
       PJ.razaoSocial as razaoSocial
from Cliente as Cli
     left join
     EnderecoCidadeUF as E on Cli.endereco = E.idEndereco
     left join
     PessoaFisica as PF on PF.idCliente = Cli.idCliente
     left join
     Sexo on Sexo.idSexo = PF.sexo
     left join
     Condutor as Cond on Cli.idCliente = Cond.idCliente
     left join
     PessoaJuridica as PJ on PJ.idCliente = Cli.idCliente
where Cli.ativo = 1

go


--- Dados do Modelo ---
--- Todos os dados de todos os modelos ativos, incluindo os dados de tipo de modelo
create view DadosModelo as
select
    Modelo.idModelo as idModelo,
    Modelo.marca as marca,
    Modelo.nome as nomeModelo,
    Modelo.categoria as categoria,
    Modelo.precoDiaria as precoDiaria,
    Modelo.precoKm as precoKm,
    TipoModelo.nome as nomeTipoModelo,
    ModeloUtilitario.carga as carga,
    ModeloUtilitario.lotacao as lotacao
from Modelo
     join
     TipoModelo on Modelo.tipoModelo = TipoModelo.idTipoModelo
     left join
     ModeloUtilitario on ModeloUtilitario.idModelo = Modelo.idModelo and
                         ModeloUtilitario.idTipoModelo = TipoModelo.idTipoModelo
where Modelo.ativo = 1

go

-- Todos os veículos que estão alugados atualmente.
create view VeiculosAlugados as
select V.idVeiculo as idVeiculo,
       V.modelo as idModelo,
       F.idFilial as idFilial,
       L.idLocacao as idLocacao,
       R.idReserva as idReserva,
       V.placa as placa,
       F.nome as nomeFilial,
       L.dataObtencao as dataObtencao,
       DC.idCliente as idCliente,
       DC.nomeCliente as nomeCliente,
       L.kilometragemObtencao as kmObtencao
from Veiculo as V
     join
     Locacao as L
     on (L.veiculo = V.idVeiculo and L.dataDevolucao is null)
     join
     Reserva as R
     on R.idReserva = L.efetivacao
     join
     Filial as F
     on R.filialObtencao = F.idFilial
     join
     DadosCliente as DC
     on R.cliente = DC.idCliente

go

create view VeiculosNaoAlugados as
(select idVeiculo, placa, modelo from Veiculo)
except
(select idVeiculo, placa, idModelo as modelo from VeiculosAlugados)

go

--- Dados do Veículo ---
--- Todos os dados de todos os veículos ativos, incluindo o modelo e a filial
create view DadosVeiculo as
select
    Veiculo.idVeiculo as idVeiculo,
    Veiculo.placa as placa,
    Veiculo.kilometragemInicial as kilometragemInicial,
    Veiculo.kilometragemAtual as kilometragemAtual,
    Veiculo.vencimentoSeguro as vencimentoSeguro,
    Veiculo.ano as ano,
    Veiculo.custoCompra as custoCompra,
    DadosModelo.idModelo as idModelo,
    DadosModelo.nomeModelo as nomeModelo,
    DadosModelo.marca as marca,
    DadosModelo.categoria as categoria,
    DadosModelo.precoDiaria as precoDiaria,
    DadosModelo.precoKm as precoKm,
    DadosModelo.nomeTipoModelo as nomeTipoModelo,
    DadosModelo.carga as carga,
    DadosModelo.lotacao as lotacao, 
    Filial.nome as nomeFilial,
    Filial.idFilial as idFilial
from Veiculo
     join
     DadosModelo on Veiculo.modelo = DadosModelo.idModelo
     join
     Filial on Veiculo.filial = Filial.idFilial
where Veiculo.ativo = 1

go

--- Dados da Filial ---
create view DadosFilial as
select
    Filial.idFilial as idFilial,
    Filial.nome as nome,
  
    Endereco.logradouro as logradouro,
    Endereco.numero as numero,
    Endereco.complemento as complemento,
    Endereco.bairro as bairro,
    Endereco.CEP as CEP,
    Endereco.outrasInformacoes as outrasInformacoes,
    Endereco.cidade as nomeCidade,
    
    UF.nome as nomeUF
from Filial
     left join
     Endereco on Filial.endereco = Endereco.idEndereco
     left join
     UF on Endereco.UF = UF.UF
where Filial.ativo = 1

go

-- Funções referentes à decisão de quais modelos estão disponíveis para reserva.

CREATE FUNCTION ModelosIndisponiveis_Res (@dataInicio datetime, @dataFim datetime, @filial int)
RETURNS TABLE
AS
return (
  select M.idModelo as idModelo,
         M.nome as nomeModelo,
         count(*) as quantidade
  from Reserva as R
       join
       Modelo as M
       on (R.modelo = M.idModelo and
           R.cancelada = 0 and
           ((@dataInicio <= R.previsaoInicio and R.previsaoInicio <= @dataFim) or
            (R.previsaoInicio <= @dataInicio and @dataInicio <= R.previsaoTermino)) and
           R.filialObtencao = @filial)
       left join
       Locacao as L
       on L.efetivacao = R.idReserva
  where L.efetivacao is null and
        R.ativo = 1
  group by M.idModelo, M.nome
);

go

CREATE FUNCTION ModelosIndisponiveis_Loc (@filial int)
RETURNS TABLE
AS
return (
  select M.idModelo as idModelo,
         M.nome as nomeModelo,
         count(*) as quantidade
  from Locacao as L
       join
       Veiculo as V
       on V.idVeiculo = L.veiculo
       join
       Modelo as M
       on M.idModelo = V.modelo
  where L.dataDevolucao is null
  group by M.idModelo, M.nome
);

go

CREATE FUNCTION TodosModelos (@filial int)
RETURNS TABLE
AS
return (
  select M.idModelo as idModelo,
         M.nome as nomeModelo,
         count(*) as quantidade
  from Veiculo as V
       join
       Filial as F
       on (V.filial = F.idFilial and F.idFilial = @filial)
       join
       Modelo as M
       on V.modelo = M.idModelo
  group by M.idModelo, M.nome
);

go

create function ModelosDisponibilidade (@dataInicio datetime, @dataFim datetime, @filial int)
returns table
as
return (
  select TM.idModelo as idModelo,
         TM.nomeMOdelo as nomeModelo,
         TM.quantidade as quantidadeTotal,
         MIR.quantidade as quantidadeReservada,
         MIL.quantidade as quantidadeAlocada
  from TodosModelos(@filial) as TM
       left join ModelosIndisponiveis_Res(@dataInicio, @dataFim, @filial) as MIR
       on TM.idModelo = MIR.idModelo
       left join ModelosIndisponiveis_Loc(@filial) as MIL
       on TM.idModelo = MIL.idModelo
);

go

CREATE FUNCTION ModelosDisponiveis (@dataInicio datetime, @dataFim datetime, @filial int)
RETURNS TABLE
AS
return (
  select *
  from ModelosDisponibilidade(@dataInicio, @dataFim, @filial) as MD
  where (quantidadeAlocada is null and quantidadeReservada is null) or
        (quantidadeAlocada is null and quantidadeTotal > quantidadeReservada) or
        (quantidadeReservada is null and quantidadeTotal > quantidadeAlocada) or
        (quantidadeTotal > quantidadeReservada + quantidadeAlocada)
);

go

create function FrotaAlugada (@filial int)
returns table
as
return (
  select *
  from VeiculosAlugados
  where idFilial = @filial
);

go

create function TodaFrota (@filial int)
returns table
as
return (
  select V.idVeiculo as idVeiculo,
         V.placa as placa
  from Veiculo as V
       join
       Filial as F
       on (F.idFilial = V.filial and F.idFilial = @filial)
);

go

create function FrotaDisponivel (@filial int)
returns table
as
return (
  (select * from TodaFrota(@filial))
  except
  (select idVeiculo, placa from FrotaAlugada(@filial))
);

go

create function FrotaDisponivelPorMarca (@filial int, @marca varchar(256))
returns table
as
return (
  select FD.idVeiculo as idVeiculo,
         FD.placa as placa
  from FrotaDisponivel(@filial) as FD
       join
       Veiculo as V
       on V.idVeiculo = FD.idVeiculo
       join
       Modelo as M
       on M.idModelo = V.modelo and M.marca = @marca
);

go

create view LocacoesNaoQuitadas as
select L.idLocacao as idLocacao,
       L.dataObtencao as dataObtencao,
       L.dataDevolucao as dataDevolucao,
       V.idVeiculo as idVeiculo,
       V.placa as placa,
       DC.email as email,
       DC.nomeCliente as nomeCliente,
       DC.CPF as CPF,
       DC.CNPJ as CNPJ,
       DC.razaoSocial as razaoSocial,
       DC.idCliente as idCliente,
       R.filialObtencao as filialObtencao,
       R.filialDevolucao as filialDevolucao,
       F1.nome as nomeFilialObtencao,
       F2.nome as nomeFilialDevolucao
from Locacao as L
     join
     Veiculo as V
     on L.veiculo = V.idVeiculo
     join
     Reserva as R
     on R.idReserva = L.efetivacao
     join
     Filial as F1
     on R.filialObtencao = F1.idFilial
     join
     Filial as F2
     on R.filialDevolucao = F2.idFilial
     join
     DadosCliente as DC
     on R.cliente = DC.idCliente
     left join
     Pagamento as P
     on L.idLocacao = P.locacao
where P.idPagamento is null

go

create view TodasReservas as
select 
       R.idReserva as idReserva,
       R.modelo as idModelo,
       R.cliente as idCliente,
       R.filialObtencao as idfilialObtencao,
       R.filialDevolucao as idfilialDevolucao,              
       M.nome as nomeModelo,       
       DC.tipoCliente as tipoCliente,
       DC.nomeCliente as nomeCliente,
       DC.razaoSocial as razaoSocial,       
       R.previsaoInicio as previsaoInicio,
       R.previsaoTermino as previsaoTermino,       
       FObt.nome as nomeFilialObtencao,
       FDev.nome as nomeFilialDevolucao
       
from Reserva as R     
     join
     Modelo as M
     on R.modelo = M.idModelo     
     join
     DadosCliente as DC
     on R.cliente = DC.idCliente     
     join
     Filial as FObt
     on R.filialObtencao = FOBt.idFilial      
     join
     Filial as FDev
     on R.filialDevolucao = FDev.idFilial     
     left join
     Locacao as L
     on R.idReserva = L.efetivacao
     
where
    L.efetivacao is null 
    and
    R.cancelada = 0
    and
    R.ativo = 1
     
go

create function ReservasUsuario(@usuario int)
returns table
return (
  select *
  from TodasReservas
  where idCliente = @usuario
);

go

create function FrotaDisponivelReserva(@reserva int)
returns table
return (
  select V.idVeiculo as idVeiculo,
         V.placa as placa,
         V.kilometragemAtual as kmAtual
  from TodasReservas as TR
       join
       Filial as F
       on F.idFilial = TR.idfilialObtencao
       join
       Veiculo as V
       on V.filial = F.idFilial and V.modelo = TR.idModelo
  where TR.idReserva = @reserva
);

go

create function FrotaAlugadaPorCliente(@idCliente int)
returns table
return (
  select *
  from VeiculosAlugados as VA
  where VA.idCliente = @idCliente
);

go

create function DadosPagamento(@locacao int)
returns table
return (
  select M.precoDiaria as precoDiaria,
         M.precoKm as precoKm,
         L.kilometragemObtencao as kmObtencao,
         L.kilometragemDevolucao kmDevolucao,
         L.dataobtencao as dataObtencao,
         L.dataDevolucao as dataDevolucao
  from Locacao as L
       join
       Veiculo as V
       on L.veiculo = V.idVeiculo
       join
       Modelo as M
       on V.modelo = M.idModelo
  where L.idLocacao = @locacao
);

go

create function FrotaDisponivelMarca(@dataInicio datetime, @dataFim dateTime, @filial int)
returns table
return (
  select M.marca as marca,
         Sum(MD.quantidadeTotal) as quantidadeTotal,
         Sum(MD.quantidadeReservada) as quantidadeReservada,
         Sum(MD.quantidadeAlocada) as quantidadeAlocada
  from ModelosDisponibilidade(@dataInicio, @dataFim, @filial) as MD
       join
       Modelo as M
       on (M.idModelo = MD.idModelo)
  group by M.marca
);

go


-- Pagas e devolvidas.
create view LocacoesTerminadas as
select *
from Locacao as L
     join
     Pagamento as P
     on P.locacao = L.idLocacao
where L.dataDevolucao is not null

go

create view LocacoesTerminadasMesAnoTotal as
select datepart(month, L.dataObtencao) as mes,
       datepart(year, L.dataObtencao) as ano,
       count(*) as quantidade
from Locacao as L
     join
     Pagamento as P
     on P.locacao = L.idLocacao
where L.dataDevolucao is not null
group by datepart(month, L.dataObtencao), datepart(year, L.dataObtencao)

go

create view LocacoesTerminadasMesAnoFilial as
select datepart(month, L.dataObtencao) as mes,
       datepart(year, L.dataObtencao) as ano,
       count(*) as quantidade,
       F.nome as nomeFilial,
       F.idFilial as idFilial
from Locacao as L
     join
     Pagamento as P
     on P.locacao = L.idLocacao
     join
     Reserva as R
     on L.efetivacao = R.idReserva
     join
     Filial as F
     on R.filialObtencao = F.idFilial
where L.dataDevolucao is not null
group by datepart(month, L.dataObtencao), datepart(year, L.dataObtencao), F.nome, F.idFilial

go

create view ClientesConduzindo as
select DC.idCliente as idCliente,
       DC.nomeCliente as nomeCliente,
       DC.CPF as CPF,
       DC.CNH as CNH,
       L.idLocacao as idLocacao,
       null as razaoSocial,
       null as CNPJ
from DadosCliente as DC
     join
     Locacao as L
     on L.condutor = DC.idCliente
where L.dataDevolucao is null

go

create view ClientesComReservaOuLocacao as
select DC.idCliente as idCliente,
       DC.nomeCliente as nomeCliente,
       DC.CPF as CPF,
       DC.razaoSocial as razaoSocial,
       DC.CNPJ as CNPJ,
       L.idLocacao as idLocacao,
       R.idReserva as idReserva
from DadosCliente as DC
     join
     Reserva as R
     on
     R.cliente = DC.idCliente
     left join
     Locacao as L
     on L.efetivacao = R.idReserva
where (L.idLocacao is null  or L.dataDevolucao is null)
      and (R.ativo = 1 and R.cancelada = 0)

go

create view ClientesCompromissados as
(select idCliente, nomeCliente, CPF, razaoSocial, CNPJ from ClientesConduzindo)
union
(select idCliente, nomeCliente, CPF, razaoSocial, CNPJ from ClientesComReservaOuLocacao)

go
